﻿using System;
using System.IO;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Resources;

using CE.User;
using CE.Global;
using CE.Database;
using CE.Report;

public partial class telereport_trueyou_sales_summary : System.Web.UI.Page
{
    string PageCode = "GGRPTTMTUSALE";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session[CE.Global.General.UserId] == null)
        {
            Response.Redirect("~/ManagementSecureLogin.aspx");
        }

        if (!IsPostBack)
        {
            if (Convert.ToInt64(Session[General.UserId].ToString()) != 1)
            {
                CheckPageAccess();
            }
            if (Session[CE.Global.General.UserRole] != null && Session[CE.Global.General.UserRole].ToString() == "200")
            {
                hplBacktoMenu.Visible = true;
            }
            else
            {
                hplBacktoMenu.Visible = false;
            }
                       

            lblError.Text = "";
            lblContent.Text = "";
            ddlDay1.Visible = true;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
            ddlYear1.Items.Clear();
            ddlYear2.Items.Clear();

            int _cryr = CE.Global.General.GetUTCDateTime().Year;
            int _styr = 2010;
            for (int _year = _cryr; _year >= _styr; _year--)
            {
                ddlYear1.Items.Add(new ListItem(_year.ToString(), _year.ToString()));
                ddlYear2.Items.Add(new ListItem(_year.ToString(), _year.ToString()));
            }

            ddlDay1.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
            ddlMonth1.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
            ddlYear1.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
            ddlDay2.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
            ddlMonth2.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
            ddlYear2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
            ddlReportType_SelectedIndexChanged(null, null);
        }
    }

    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        lblError.Text = "";
        txtReportType.Text = ddlReportType.Text;
        LoadGrid();
    }

    protected void btnDownLoad_Click(object sender, EventArgs e)
    {
        GenerateExcel();
    }

    protected void ddlReportType_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblError.Text = "";
        ddlDay1.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
        ddlDay2.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
        ddlMonth1.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
        ddlMonth2.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
        ddlYear1.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        ddlYear2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        if (Convert.ToInt16(ddlReportType.Text) == 0)
        {
            ddlDay1.Visible = false;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 1)
        {
            ddlDay1.Visible = true;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 2)
        {
            lblFromDate.Text = "From Date :";
            lblToDate.Text = " To Date :";
            lblToDate.Visible = true;
            ddlDay2.Visible = true;
            ddlMonth2.Visible = true;
            ddlYear2.Visible = true;
            ddlDay1.Visible = true;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 3)
        {
            ddlDay1.Visible = false;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 4)
        {
            ddlDay1.Visible = false;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
    }

    protected void grvSalesSummary_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            e.Row.Cells[0].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[1].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[2].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[7].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[8].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[10].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[12].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[5].Visible = false;
            e.Row.Cells[6].Visible = false;
            e.Row.Cells[9].Visible = false; 
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[0].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[1].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[2].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[7].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[8].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[10].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[12].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[5].Visible = false;
            e.Row.Cells[6].Visible = false;
            e.Row.Cells[9].Visible = false; 
        }
    }

    public bool ValidDate(Int32 Year, Int32 Month, Int32 Day)
    {
        bool _isValid = true;
        int daysInMonth = 0;
        string leap, monthValid, dayValid, yearValid;
        if (Year >= 0000)
        {
            yearValid = "yes";
        }
        else
        {
            yearValid = "no";
        }
        if (Year % 400 == 0)
        {
            leap = "yes";
        }
        else if (Year % 100 == 0)
        {
            leap = "no";
        }
        else if (Year % 4 == 0)
        {
            leap = "yes";
        }
        else
        {
            leap = "no";
        }

        switch (Month)
        {
            case 1: daysInMonth = 31;
                break;
            case 3: daysInMonth = 31;
                break;
            case 4: daysInMonth = 30;
                break;
            case 5: daysInMonth = 31;
                break;
            case 6: daysInMonth = 30;
                break;
            case 7: daysInMonth = 31;
                break;
            case 8: daysInMonth = 31;
                break;
            case 9: daysInMonth = 30;
                break;
            case 10: daysInMonth = 31;
                break;
            case 11: daysInMonth = 30;
                break;
            case 12: daysInMonth = 31;
                break;
        }

        if (Month >= 1 && Month <= 12)
        {
            monthValid = "yes";
        }
        else
        {
            monthValid = "no";
        }
        if (Month == 2)
        {
            if (leap == "no")
            {
                daysInMonth = 28;
            }
            else if (leap == "yes")
            {
                daysInMonth = 29;
            }
        }

        if (1 <= Day && Day <= daysInMonth)
        {
            dayValid = "yes";
        }

        else
        {
            dayValid = "no";
        }

        if ((dayValid == "yes") && (monthValid == "yes") && (yearValid == "yes"))
        {
            _isValid = true;
        }
        else
        {
            _isValid = false;
        }

        return _isValid;
    }

    public string GetMonth(string month)
    {
        string result = "";
        if (month == "01")
        {
            result = " Jan ";
        }
        else if (month == "02")
        {
            result = " Feb ";
        }
        else if (month == "03")
        {
            result = " Mar ";
        }
        else if (month == "04")
        {
            result = " Apr ";
        }
        else if (month == "05")
        {
            result = " May ";
        }
        else if (month == "06")
        {
            result = " Jun ";
        }
        else if (month == "07")
        {
            result = " Jul ";
        }
        else if (month == "08")
        {
            result = " Aug ";
        }
        else if (month == "09")
        {
            result = " Sep ";
        }
        else if (month == "10")
        {
            result = " Oct ";
        }
        else if (month == "11")
        {
            result = " Nov ";
        }
        else if (month == "12")
        {
            result = " Dec ";
        }
        return result;
    }

    public string LastDayOfMonth(string ReportDate)
    {
        string result = "31";
        DateTime dtReportDate = Convert.ToDateTime(ReportDate);
        DateTime today = dtReportDate;
        DateTime endOfMonth = new DateTime(today.Year, today.Month, DateTime.DaysInMonth(today.Year, today.Month));
        result = endOfMonth.Day.ToString("00");
        return result;
    }

    public bool Validatioin()
    {
        lblError.Text = "";
        bool _isValid = true;
        DateTime _fromDate, _toDate;
        if (Convert.ToInt16(ddlReportType.Text) == 1)
        {
            if (ddlDay1.SelectedIndex >= 0)
            {
                if (ValidDate(Convert.ToInt32(ddlYear1.Text), Convert.ToInt32(ddlMonth1.Text), Convert.ToInt32(ddlDay1.Text)))
                {
                    _fromDate = Convert.ToDateTime(Convert.ToInt32(ddlYear1.Text) + "/" + Convert.ToInt32(ddlMonth1.Text) + "/" + Convert.ToInt32(ddlDay1.Text));
                    if (_fromDate > CE.Global.General.GetUTCDateTime())
                    {
                        _isValid = false;
                        lblError.Text = "Date should be less than or equal to Today";
                    }
                }
                else
                {
                    _isValid = false;
                    lblError.Text = "Select Valid Date";
                }
            }
            else
            {
                _isValid = false;
                lblError.Text = "Select Date";
            }
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 2)
        {
            if (ddlDay1.SelectedIndex >= 0 && ddlDay2.SelectedIndex >= 0)
            {
                if (ValidDate(Convert.ToInt32(ddlYear1.Text), Convert.ToInt32(ddlMonth1.Text), Convert.ToInt32(ddlDay1.Text)))
                {
                    _fromDate = Convert.ToDateTime(Convert.ToInt32(ddlYear1.Text) + "/" + Convert.ToInt32(ddlMonth1.Text) + "/" + Convert.ToInt32(ddlDay1.Text));
                    if (ValidDate(Convert.ToInt32(ddlYear2.Text), Convert.ToInt32(ddlMonth2.Text), Convert.ToInt32(ddlDay2.Text)))
                    {
                        _toDate = Convert.ToDateTime(Convert.ToInt32(ddlYear2.Text) + "/" + Convert.ToInt32(ddlMonth2.Text) + "/" + Convert.ToInt32(ddlDay2.Text));
                        if (_fromDate <= CE.Global.General.GetUTCDateTime())
                        {
                            if (_fromDate > _toDate)
                            {
                                _isValid = false;
                                lblError.Text = "From date should be less than To date";
                            }
                        }
                        else
                        {
                            _isValid = false;
                            lblError.Text = "From date should be less than Today";
                        }
                    }
                    else
                    {
                        _isValid = false;
                        lblError.Text = "Select Valid To date";
                    }
                }
                else
                {
                    _isValid = false;
                    lblError.Text = "Select Valid From date";
                }
            }
            else
            {
                _isValid = false;
                lblError.Text = "Select From date and To date";
            }
        }
        return _isValid;
    }

    public void LoadGrid()
    {
        lblError.Text = "";
        if (Validatioin() == true)
        {
            txtReportDate1.Text = "";
            txtReportDate2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000") + CE.Global.General.GetUTCDateTime().Month.ToString("00") + CE.Global.General.GetUTCDateTime().Day.ToString("00");
            txtReportDate1.Text = ddlYear1.Text;
            txtReportDate1.Text += ddlMonth1.Text;
            txtReportDate1.Text += ddlDay1.Text;
            if (Convert.ToInt16(ddlReportType.Text) == 0)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
                txtReportDate1.Text += "01";
            }
            else if (Convert.ToInt16(ddlReportType.Text) == 1)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
                txtReportDate1.Text += ddlDay1.Text;
            }
            else if (Convert.ToInt16(ddlReportType.Text) == 2)
            {
                txtReportDate2.Text = ddlYear2.Text;
                txtReportDate2.Text += ddlMonth2.Text;
                txtReportDate2.Text += ddlDay2.Text;
            }
            DataSet _dsSummary = new DataSet();
            DataTable _dtSummary = new DataTable();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();
            if (_ReportDate1.Length == 6)
            {
                txtReportDate.Text = _ReportDate1.ToString() + "01";
            }
            else if (_ReportDate1.Length == 8)
            {
                txtReportDate.Text = _ReportDate1.ToString();
            }
            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "[sprocCXCRMTrueYouSalesSummarySelectedList]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ReportType", _ReportType);
            _sPara.Add("@ReportDate1", _ReportDate1);
            _sPara.Add("@ReportDate2", _ReportDate2);

            _dsSummary = _db.GetDataSet(_spName, _sPara);
            if (_dsSummary != null && _dsSummary.Tables.Count > 0 && _dsSummary.Tables[0].Rows.Count > 0)
            {
                _dtSummary = _dsSummary.Tables[0];
                grvSalesSummary.DataSource = _dtSummary;
                grvSalesSummary.DataBind();
                EnablePrintView();
            }
            else
            {
                grvSalesSummary.DataSource = null;
                grvSalesSummary.DataBind();
                EnablePrintView();
            }
        }
        else
        {
            grvSalesSummary.DataSource = null;
            grvSalesSummary.DataBind();
            EnablePrintView();
        }
    }

    private void EnablePrintView()
    {
        if (grvSalesSummary.Rows.Count > 0)
        {
            ddlLanguage.Visible = false;
            btnDownLoad.Visible = true;
            lblRecordCount.Text = "(" + grvSalesSummary.Rows.Count.ToString() + ") record(s). Date/Time: " + CE.Global.General.GetUTCDateTime().ToString("dd-MMM-yyyy hh:mm tt") + "&nbsp;&nbsp;";
        }
        else
        {
            btnDownLoad.Visible = false;
            lblRecordCount.Text = "";
        }
    }

    public void GenerateExcel()
    {
        string _html = "";
        string _fileName = "TrueYouSalesSummary@" + CE.Global.General.GetUTCDateTime().Day.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Month.ToString("00") + "-" + CE.Global.General.GetUTCDateTime().Year;
        lblContent.Text = "";
        lblError.Text = "";
        if (Validatioin() == true)
        {
            txtReportDate1.Text = "";
            txtReportDate2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000") + CE.Global.General.GetUTCDateTime().Month.ToString("00") + CE.Global.General.GetUTCDateTime().Day.ToString("00");
            txtReportDate1.Text = ddlYear1.Text;
            txtReportDate1.Text += ddlMonth1.Text;
            txtReportDate1.Text += ddlDay1.Text;
            if (Convert.ToInt16(ddlReportType.Text) == 0)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
                txtReportDate1.Text += "01";
            }
            else if (Convert.ToInt16(ddlReportType.Text) == 1)
            {
                txtReportDate1.Text = "";
                txtReportDate1.Text = ddlYear1.Text;
                txtReportDate1.Text += ddlMonth1.Text;
                txtReportDate1.Text += ddlDay1.Text;
            }
            else if (Convert.ToInt16(ddlReportType.Text) == 2)
            {
                txtReportDate2.Text = ddlYear2.Text;
                txtReportDate2.Text += ddlMonth2.Text;
                txtReportDate2.Text += ddlDay2.Text;
            }


            Int32 OnlineCount = 0;
            Int32 OfflineCount = 0;
            Int32 TotalFileCount = 0;
            DataSet _dsSummary = new DataSet();
            DataTable _dtSummary = new DataTable();
            DataTable _dtCMISummary = new DataTable();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();

            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "[sprocCXCRMTrueYouSalesSummarySelectedList]";
            SortedList _sPara = new SortedList();

            _sPara.Add("@ReportType", _ReportType);
            _sPara.Add("@ReportDate1", _ReportDate1);
            _sPara.Add("@ReportDate2", _ReportDate2);
            _dsSummary = _db.GetDataSet(_spName, _sPara);
            if (_dsSummary != null && _dsSummary.Tables.Count > 0 && _dsSummary.Tables[0].Rows.Count > 0)
            {
                _dtSummary = _dsSummary.Tables[0];
                TotalFileCount += _dtSummary.Rows.Count;
            }
            if (TotalFileCount > 0)
            {
                #region Variable Declaration
                string PaymentType = "";
                decimal dec_CollectedPreium, dec_TotalCollectedPreium;
                string[] OrderDate = new string[TotalFileCount];
                string[] TypeOfLeads = new string[TotalFileCount];
                string[] InsuranceType = new string[TotalFileCount];
                string[] Insurer = new string[TotalFileCount];
                string[] CustomerName = new string[TotalFileCount];
                string[] PolicyStartDate = new string[TotalFileCount];
                string[] PolicyExpiryDate = new string[TotalFileCount];
                string[] Tier = new string[TotalFileCount];
                string[] CollectedPremium = new string[TotalFileCount];
                string[] PremiumReceivingDate = new string[TotalFileCount];
                string[] IdentificationNo = new string[TotalFileCount];
                string[] CarPlate = new string[TotalFileCount];
                string[] QuoteRefNo = new string[TotalFileCount];
                string[] CarId = new string[TotalFileCount];
                string[] Agent = new string[TotalFileCount];
                string[] AgentEN = new string[TotalFileCount];
                string[] Status = new string[TotalFileCount];
                string[] ProviderCompanyId = new string[TotalFileCount];
                #endregion

                #region Value Assign
                dec_TotalCollectedPreium = 0;
                for (int i = 0; i < TotalFileCount; i++)
                {
                    dec_CollectedPreium = 0;
                    OrderDate[i] = _dtSummary.Rows[i]["OrderDate"].ToString();
                    TypeOfLeads[i] = _dtSummary.Rows[i]["TypeOfLeads"].ToString();
                    InsuranceType[i] = _dtSummary.Rows[i]["InsuranceType"].ToString();
                    Insurer[i] = _dtSummary.Rows[i]["Insurer"].ToString();
                    CustomerName[i] = _dtSummary.Rows[i]["CustomerName"].ToString();
                    PolicyStartDate[i] = _dtSummary.Rows[i]["PolicyStartDate"].ToString();
                    PolicyExpiryDate[i] = _dtSummary.Rows[i]["PolicyExpiryDate"].ToString();
                    Tier[i] = _dtSummary.Rows[i]["Tier"].ToString();
                    CollectedPremium[i] = _dtSummary.Rows[i]["CollectedPremium"].ToString();
                    PremiumReceivingDate[i] = _dtSummary.Rows[i]["PremiumReceivingDate"].ToString();
                    IdentificationNo[i] = _dtSummary.Rows[i]["IdentificationNo"].ToString();
                    CarPlate[i] = _dtSummary.Rows[i]["CarPlate"].ToString();
                    QuoteRefNo[i] = _dtSummary.Rows[i]["QuoteRefNo"].ToString();
                    CarId[i] = _dtSummary.Rows[i]["CarId"].ToString();
                    Agent[i] = _dtSummary.Rows[i]["Agent"].ToString();
                    AgentEN[i] = _dtSummary.Rows[i]["AgentEN"].ToString();
                    Status[i] = _dtSummary.Rows[i]["Status"].ToString();
                    ProviderCompanyId[i] = _dtSummary.Rows[i]["ProviderCompanyId"].ToString();
                    if (!Decimal.TryParse(_dtSummary.Rows[i]["CollectedPremium"].ToString(), out dec_CollectedPreium))
                    {
                        dec_CollectedPreium = 0;
                    }
                    dec_TotalCollectedPreium = dec_TotalCollectedPreium + dec_CollectedPreium;
                }
                #endregion

                #region Generate Excel
                string ReportMonth = "";
                if (_ReportType == 0)
                {
                    ReportMonth = GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                }
                else if (_ReportType == 1)
                {
                    ReportMonth = ddlDay1.Text + " " + GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text;
                }
                else if (_ReportType == 2)
                {
                    ReportMonth = ddlDay1.Text + " " + GetMonth(ddlMonth1.Text).Trim() + " " + ddlYear1.Text + " TO " + ddlDay2.Text + " " + GetMonth(ddlMonth2.Text).Trim() + " " + ddlYear2.Text;
                }
                _html += "<html>";
                _html += "<head>";
                _html += "<meta http-equiv='content-type' content='text/html; charset=UTF-8'>";
                _html += "</head>";
                _html += "<body>";
                _html += "<table width='100%' cellpadding='1' cellspacing='1' border='1'>";

                _html += "<tr><td align='left' colspan='16' style='font-size:20px; background-color:#D8C7FF;'><b>TrueYou Sales Summary (" + ReportMonth + ")</b></td></tr>";
                _html += "<tr>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Order Date</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Type of Leads</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Insurance Type</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Insurer</b></td>";
                _html += "<td width='350' height='40' style='text-align: left; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Customer Name</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Policy Start Date</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Policy Expiry Date</b></td>";
                _html += "<td width='60' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Tier</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Collected Premium</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Premium Receiving Date</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>ID / Passport</b></td>";
                _html += "<td width='100' height='40' style='text-align: left; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Car Plate</b></td>";
                _html += "<td width='80' height='40' style='text-align: left; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Reference No.</b></td>";
                _html += "<td width='60' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>CarId</b></td>";
                _html += "<td width='120' height='40' style='text-align: left; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Agent</b></td>";
                _html += "<td width='80' height='40' style='text-align: center; vertical-align:middle; font-size:14px; background-color:#FFFF00;'><b>Status</b></td>";
                _html += "</tr>";
                
                    
                
                for (int j = 0; j < TotalFileCount; j++)
                {
                    _html += "<tr>";
                    _html += "<td width='80' align='center' style='vertical-align:middle; mso-number-format:dd\\-mmm\\-yyyy'>" + OrderDate[j].ToString() + "</td>";
                    _html += "<td width='80' align='center' style='vertical-align:middle; background-color:#FFFF9E;'>" + TypeOfLeads[j].ToString() + "</td>";
                    _html += "<td width='80' align='center' style='vertical-align:middle;'>" + InsuranceType[j].ToString() + "</td>";
                    _html += "<td width='80' align='center' style='vertical-align:middle;'>" + Insurer[j].ToString() + "</td>";
                    _html += "<td width='350' align='left' style='vertical-align:middle;'>" + CustomerName[j].ToString() + "</td>";
                    _html += "<td width='80' align='center' style='vertical-align:middle; mso-number-format:dd\\-mmm\\-yyyy'>" + PolicyStartDate[j].ToString() + "</td>";
                    _html += "<td width='80' align='center' style='vertical-align:middle; mso-number-format:dd\\-mmm\\-yyyy'>" + PolicyExpiryDate[j].ToString() + "</td>";
                    _html += "<td width='60' align='center' style='vertical-align:middle; background-color:#FFFF9E;'>" + Tier[j].ToString() + "</td>";
                    _html += "<td width='100' align='right' style='vertical-align:middle; background-color:#C7FFE6; mso-number-format:\\#\\,\\#\\#0\\.00'>" + CollectedPremium[j].ToString() + "</td>";
                    _html += "<td width='80' align='center' style='vertical-align:middle; mso-number-format:dd\\-mmm\\-yyyy'>" + PremiumReceivingDate[j].ToString() + "</td>";
                    _html += "<td width='80' align='left' style='vertical-align:middle; mso-number-format:\\@'>" + IdentificationNo[j].ToString() + "</td>";
                    _html += "<td width='100' align='left' style='vertical-align:middle;'>" + CarPlate[j].ToString() + "</td>";
                    _html += "<td width='80' align='center' style='vertical-align:middle;'>" + QuoteRefNo[j].ToString() + "</td>";
                    _html += "<td width='120' align='left' style='vertical-align:middle; background-color:#FFFF9E;'>" + CarId[j].ToString() + "</td>";
                    _html += "<td width='120' align='left' style='vertical-align:middle;'>" + Agent[j].ToString() + "</td>";
                    _html += "<td width='80' align='center' style='vertical-align:middle; background-color:#FFFF9E;'>" + Status[j].ToString() + "</td>";
                    _html += "</tr>";
                }
                _html += "</table>";

                
                #region Total
                _html += "<table width='100%' cellpadding='1' cellspacing='1' border='1'>";
                _html += "<tr>";

                _html += "<td colspan='8' align='right' style='font-size:17px; background-color:#FFE100;'><b>Total</b></td>";
                _html += "<td width='100px' align='right' style='font-size:17px; background-color:#FFE100; mso-number-format:\\#\\,\\#\\#0\\.00'><b>" + dec_TotalCollectedPreium.ToString() + "</b></td>";
                _html += "<td colspan='7' style='font-size:17px; background-color:#FFE100;'>&nbsp;</td>";
                _html += "</tr>";
                _html += "</table>";
                #endregion

                _html += "</body>";
                _html += "</html>";
                #endregion

                lblContent.Text = _html;
                ExportGridView(_fileName, lblContent);
            }
        }
    }

    public void ExportGridView(string fileName, Label lblContent)
    {
        string attachment = "attachment; filename=" + fileName + ".xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";
        Response.Write(lblContent.Text);
        Response.End();
    }

    public DataTable GetPercentage(string PremiumLevel, string ReportDate)
    {
        MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
        SortedList _sPara = new SortedList();
        string _spName = "sprocCXCRMAgentCommissionSelectSingleItem";
        _sPara.Add("@PremiumLevel", PremiumLevel);
        _sPara.Add("@ReportDate", ReportDate);
        DataTable _dtPercentage = new DataTable();
        _dtPercentage = _db.GetDataTableBySP(_spName, _sPara);
        return _dtPercentage;
    }

    public string GetReportMonth(string ReportMonth)
    {
        string month = ReportMonth.Substring(4, 2);
        string year = ReportMonth.Substring(0, 4);
        string result = "";
        if (month == "01")
        {
            result = " Jan ";
        }
        else if (month == "02")
        {
            result = " Feb ";
        }
        else if (month == "03")
        {
            result = " Mar ";
        }
        else if (month == "04")
        {
            result = " Apr ";
        }
        else if (month == "05")
        {
            result = " May ";
        }
        else if (month == "06")
        {
            result = " Jun ";
        }
        else if (month == "07")
        {
            result = " Jul ";
        }
        else if (month == "08")
        {
            result = " Aug ";
        }
        else if (month == "09")
        {
            result = " Sep ";
        }
        else if (month == "10")
        {
            result = " Oct ";
        }
        else if (month == "11")
        {
            result = " Nov ";
        }
        else if (month == "12")
        {
            result = " Dec ";
        }
        return result + year;
    }

    public string GetPaymentDetailList(Int64 CECarId, Int64 CXBrokerProviderId, string CollectedPremium)
    {
        int _CC = 0;
        int _CH = 0;
        int _BT = 0;
        string _PayDetails = " ";
        string _strVAT7 = "0";
        string _strBBLCharge = "0";
        string _strBBLNetReceived = "0";
        string _paymentDate = " ";
        string _paymentMode = "";
        string _strBank = "";
        string _paymentBank = "";
        float fl_CollectedPremium = Convert.ToSingle(CollectedPremium);
        MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
        SortedList _sPara = new SortedList();
        string _spName = "sprocCXCRMBrokerPremiumSelectSingleItem";
        _sPara.Add("@CXBrokerProviderId", CXBrokerProviderId);
        _sPara.Add("@CECarId", CECarId);
        _sPara.Add("@SelectedStatus", true);
        DataSet _dsPayment = new DataSet();
        DataTable _dtPayment = new DataTable();
        _dsPayment = _db.GetDataSet(_spName, _sPara);
        if (_dsPayment != null && _dsPayment.Tables.Count > 1 && _dsPayment.Tables[1].Rows.Count > 0)
        {
            _dtPayment = _dsPayment.Tables[1];
            _paymentDate = _dtPayment.Rows[0]["ModifiedDate"].ToString();
            for (int i = 0; i < _dtPayment.Rows.Count; i++)
            {
                string _paymentType = _dtPayment.Rows[i]["PaymentTypeID"].ToString().ToLower();
                _paymentMode = _dtPayment.Rows[i]["PaymentOptionId"].ToString().ToLower();
                if (_paymentType == "cash")
                {
                    _CH += 1;
                }
                else if (_paymentType == "bktr")
                {
                    _BT += 1;
                    _paymentBank = _dtPayment.Rows[i]["PaymentBank"].ToString().ToUpper();
                }
                else if (_paymentType == "ccip" || _paymentType == "ccol")
                {
                    _CC += 1;
                }
            }

            if (_CH > 0)
            {
                _PayDetails += "CASH";
            }

            if (_BT > 0)
            {
                if (_PayDetails.Trim() != string.Empty)
                {
                    _PayDetails += ",";
                }
                _PayDetails += "BKTR";

                if (_paymentBank == "KBANK")
                {
                    _strBank = "K Bank";
                }
                else if (_paymentBank == "SCB")
                {
                    _strBank = "SCB";
                }
                else if (_paymentBank == "KTB")
                {
                    _strBank = "KTB";
                }
                else
                {
                    _strBank = "BBL";
                }
            }

            if (_CC > 0)
            {
                if (_PayDetails.Trim() != string.Empty)
                {
                    _PayDetails += ",";
                }

                //_PayDetails += "CC";
                if (_paymentMode == "ip")
                {
                    _PayDetails += "CC  - IP";
                }
                else
                {
                    _PayDetails += "CC";
                }
            }

            if (_paymentMode == "ip")
            {
                if (CXBrokerProviderId == 11)
                {
                    _strBank = "AZCP";
                }
                else if (CXBrokerProviderId == 29)
                {
                    _strBank = "LMG";
                }
                else if (CXBrokerProviderId == 32)
                {
                    _strBank = "BKI";
                }
                else
                {
                    _strBank = "K Bank";
                }
            }
            else if (_paymentMode == "fp")
            {
                _strBank = "BBL";
            }

            //_PayDetails = _PayDetails + "@" + _paymentDate;
            if (_CC > 0)
            {
                _strBBLCharge = (fl_CollectedPremium * 0.03).ToString();
                _strBBLCharge = Math.Round(Convert.ToSingle(_strBBLCharge), 2).ToString();
                _strVAT7 = Math.Round((Convert.ToSingle(_strBBLCharge) * 0.07), 2).ToString();
            }
        }
        _strBBLNetReceived = Math.Round((Convert.ToSingle(fl_CollectedPremium) - Convert.ToSingle(_strBBLCharge) - Convert.ToSingle(_strVAT7)), 2).ToString();
        _PayDetails = _PayDetails + "@" + _paymentDate + "@" + _strBBLCharge + "@" + _strVAT7 + "@" + _strBBLNetReceived + "@" + _strBank;
        return _PayDetails;
    }

    public string GetPaymentDetailListNew(Int64 CECarId, Int64 CXBrokerProviderId)
    {
        int _CC = 0;
        int _CH = 0;
        int _BT = 0;
        int _CHIP = 0;
        string _PayDetails = " ";
        string _paymentMode = "";
        string _strBank = "";
        string _paymentBank = "";
        MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
        SortedList _sPara = new SortedList();
        string _spName = "sprocCXCRMBrokerPremiumSelectSingleItem";
        _sPara.Add("@CXBrokerProviderId", CXBrokerProviderId);
        _sPara.Add("@CECarId", CECarId);
        _sPara.Add("@SelectedStatus", true);
        DataSet _dsPayment = new DataSet();
        DataTable _dtPayment = new DataTable();
        _dsPayment = _db.GetDataSet(_spName, _sPara);
        if (_dsPayment != null && _dsPayment.Tables.Count > 1 && _dsPayment.Tables[1].Rows.Count > 0)
        {
            _dtPayment = _dsPayment.Tables[1];
            for (int i = 0; i < _dtPayment.Rows.Count; i++)
            {
                string _paymentType = _dtPayment.Rows[i]["PaymentTypeID"].ToString().ToLower();
                _paymentMode = _dtPayment.Rows[i]["PaymentOptionId"].ToString().ToLower();
                if (_paymentType == "cash")
                {
                    _CH += 1;
                }
                else if (_paymentType == "bktr")
                {
                    _BT += 1;
                    _paymentBank = _dtPayment.Rows[i]["PaymentBank"].ToString().ToUpper();
                }
                else if (_paymentType == "ccip" || _paymentType == "ccol")
                {
                    _CC += 1;
                }
                else if (_paymentType == "chins")
                {
                    _CHIP += 1;
                    _paymentBank = "CASH";
                    if (_dtPayment.Rows[i]["PaymentBank"] != DBNull.Value && _dtPayment.Rows[i]["PaymentBank"].ToString() != string.Empty)
                    {
                        _paymentBank = _dtPayment.Rows[i]["PaymentBank"].ToString().ToUpper();
                    }
                }
            }

            if (_CH > 0)
            {
                _PayDetails += "CASH";
            }

            if (_BT > 0)
            {
                if (_PayDetails.Trim() != string.Empty)
                {
                    _PayDetails += ",";
                }
                _PayDetails += "BKTR";

                if (_paymentBank == "KBANK")
                {
                    _strBank = "K Bank";
                }
                else if (_paymentBank == "SCB")
                {
                    _strBank = "SCB";
                }
                else if (_paymentBank == "KTB")
                {
                    _strBank = "KTB";
                }
                else
                {
                    _strBank = "BBL";
                }
            }

            if (_CC > 0)
            {
                if (_PayDetails.Trim() != string.Empty)
                {
                    _PayDetails += ",";
                }

                if (_paymentMode == "ip")
                {
                    _PayDetails += "CC - IP";
                }
                else
                {
                    _PayDetails += "CC";
                }
            }

            if (_CHIP > 0)
            {
                if (_PayDetails.Trim() != string.Empty)
                {
                    _PayDetails += ",";
                }
                _PayDetails += "CASH - IP";

                if (_paymentBank == "KBANK")
                {
                    _strBank = "K Bank";
                }
                else if (_paymentBank == "SCB")
                {
                    _strBank = "SCB";
                }
                else if (_paymentBank == "KTB")
                {
                    _strBank = "KTB";
                }
                else
                {
                    _strBank = "CASH";
                }
            }
            else if (_paymentMode == "ip")
            {
                if (CXBrokerProviderId == 11)
                {
                    _strBank = "AZCP";
                }
                else if (CXBrokerProviderId == 29)
                {
                    _strBank = "LMG";
                }
                else if (CXBrokerProviderId == 32)
                {
                    _strBank = "BKI";
                }
                else
                {
                    _strBank = "K Bank";
                }
            }
            else if (_paymentMode == "fp" && _CC > 0)
            {
                _strBank = "BBL";
            }
        }
        _PayDetails = _PayDetails + "@" + _strBank;
        return _PayDetails;
    }

    private string GetProvider(Int64 ProviderCompanyId)
    {
        string _result = "";
        if (ProviderCompanyId == 11)
        {
            _result = "AZCP";
        }
        else if (ProviderCompanyId == 12)
        {
            _result = "MSIG";
        }
        else if (ProviderCompanyId == 16)
        {
            _result = "AXA";
        }
        else if (ProviderCompanyId == 29)
        {
            _result = "LMG";
        }
        else if (ProviderCompanyId == 31)
        {
            _result = "CTS";
        }
        else if (ProviderCompanyId == 32)
        {
            _result = "BKI";
        }
        else if (ProviderCompanyId == 33)
        {
            _result = "MTI";
        }
        else if (ProviderCompanyId == 35)
        {
            _result = "KPI";
        }
        else if (ProviderCompanyId == 39)
        {
            _result = "MIR";
        }
        else if (ProviderCompanyId == 41)
        {
            _result = "KSK";// _result = "KUR";
        }
        else if (ProviderCompanyId == 43)
        {
            _result = "SMK";
        }
        else if (ProviderCompanyId == 45)
        {
            _result = "VRY";
        }
        else if (ProviderCompanyId == 46)
        {
            _result = "TMS";
        }
        else if (ProviderCompanyId == 50)
        {
            _result = "SCS";
        }
        else if (ProviderCompanyId == 58)
        {
            _result = "TVV";
        }
        else if (ProviderCompanyId == 65)
        {
            _result = "DPY";
        }
        else if (ProviderCompanyId == 51)
        {
            _result = "SMG";
        }
        else if (ProviderCompanyId == 66)
        {
            _result = "TPB";
        }
        return _result;
    }

    public string GetRemarks(string QuoteRefNo, Int16 TransactionTypeId)
    {
        string Remarks = "";
        MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
        SortedList _sPara = new SortedList();
        string _spName = "sprocCXCRMNonGGTransactionSelectItemByQuoteRefNo";
        _sPara.Add("@QuoteRefNo", QuoteRefNo);
        _sPara.Add("@TransactionTypeId", TransactionTypeId);
        DataSet _dsRemarks = new DataSet();
        _dsRemarks = _db.GetDataSet(_spName, _sPara);
        if (_dsRemarks != null && _dsRemarks.Tables.Count > 0 && _dsRemarks.Tables[0].Rows.Count > 0)
        {
            Remarks = _dsRemarks.Tables[0].Rows[0]["Remarks"].ToString();
        }
        return Remarks;
    }

    public DataTable GetPercentage(Int64 ProviderCompanyId, string PremiumType, Int64 InsuranceType, Int16 GarageType, string ReportDate)
    {
        MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
        SortedList _sPara = new SortedList();
        string _spName = "sprocCXCRMOICPercentageSelectSingleItem";
        _sPara.Add("@ProviderCompanyId", ProviderCompanyId);
        _sPara.Add("@PremiumType", PremiumType);
        _sPara.Add("@InsuranceType", InsuranceType);
        _sPara.Add("@GarageType", GarageType);
        _sPara.Add("@ReportDate", ReportDate);
        DataTable _dtPercentage = new DataTable();
        _dtPercentage = _db.GetDataTableBySP(_spName, _sPara);
        return _dtPercentage;
    }

    private void CheckPageAccess()
    {
        Int64 PageAccess = General.GetPageAccess(Convert.ToInt64(Session[General.UserId].ToString()), PageCode);
        if (PageAccess == 0)
        {
            Response.Redirect("~/UserMessage.aspx?menu=0&mt=UM&ec=101");
        }
        else
        {
            EnablePageAccess(PageAccess);
        }
    }

    private void EnablePageAccess(Int64 PageAccess)
    {
        if (PageAccess == 100)
        {
            btnDownLoad.Enabled = false;
        }
        else if (PageAccess == 200)
        {
            btnDownLoad.Enabled = true;
        }
    }

    public static string GetProviderName(Int64 ProviderCompanyId, string Language)
    {
        string _result = "";
        if (Language == "1")
        {
            if (ProviderCompanyId == 11)
            {
                _result = "อลิอันซ์ ซี.พี. ประกันภัย";
            }
            else if (ProviderCompanyId == 12)
            {
                _result = "เอ็ม เอส ไอ จี ประกันภัย";
            }
            else if (ProviderCompanyId == 16)
            {
                _result = "แอกซ่า ประกันภัย";
            }
            else if (ProviderCompanyId == 29)
            {
                _result = "แอลเอ็มจี ประกันภัย";
            }
            else if (ProviderCompanyId == 31)
            {
                _result = "นิวแฮมพ์เชอร์ อินชัวรันส์";
            }
            else if (ProviderCompanyId == 32)
            {
                _result = "กรุงเทพประกันภัย";
            }
            else if (ProviderCompanyId == 33)
            {
                _result = "เมืองไทยประกันภัย";
            }
            else if (ProviderCompanyId == 35)
            {
                _result = "กรุงไทยพานิชประกันภัย";
            }
            else if (ProviderCompanyId == 39)
            {
                _result = "มิตรแท้ประกันภัย";
            }
            else if (ProviderCompanyId == 41)
            {
                _result = "เค เอส เค";// "คูเนีย ประกันภัย";
            }
            else if (ProviderCompanyId == 43)
            {
                _result = "สินมั่นคงประกันภัย";
            }
            else if (ProviderCompanyId == 45)
            {
                _result = "วิริยะประกันภัย";
            }
            else if (ProviderCompanyId == 46)
            {
                _result = "โตเกียวมารีนศรีเมือง";
            }
            else if (ProviderCompanyId == 50)
            {
                _result = "ไทยพาณิชย์สามัคคีประกันภัย";
            }
            else if (ProviderCompanyId == 58)
            {
                _result = "ประกันภัยไทยวิวัฒน์";
            }

        }
        else if (Language == "2")
        {
            if (ProviderCompanyId == 11)
            {
                _result = "Allianz CP Insurance";
            }
            else if (ProviderCompanyId == 12)
            {
                _result = "MSIG Insurance";
            }
            else if (ProviderCompanyId == 16)
            {
                _result = "AXA Insurance";
            }
            else if (ProviderCompanyId == 29)
            {
                _result = "LMG  Insurance";
            }
            else if (ProviderCompanyId == 31)
            {
                _result = "Chartis Insurance";
            }
            else if (ProviderCompanyId == 32)
            {
                _result = "BKI Insurance";
            }
            else if (ProviderCompanyId == 33)
            {
                _result = "MuangThai Insurance";
            }
            else if (ProviderCompanyId == 35)
            {
                _result = "KrungThai Insurance";
            }
            else if (ProviderCompanyId == 39)
            {
                _result = "Mittare Insurance";
            }
            else if (ProviderCompanyId == 41)
            {
                _result = "KSK Insurance";
            }
            else if (ProviderCompanyId == 43)
            {
                _result = "Synmunkong Insurance";
            }
            else if (ProviderCompanyId == 45)
            {
                _result = "Viriyah Insurance";
            }
            else if (ProviderCompanyId == 46)
            {
                _result = "Tokio Marine Insurance";
            }
            else if (ProviderCompanyId == 50)
            {
                _result = "SCSMG Insurance";
            }
            else if (ProviderCompanyId == 58)
            {
                _result = "Thaivivat Insurance";
            }
        }
        return _result;
    }

    public string GetAgentName(string AgentNameTH, string AgentNameEN)
    {
        string AgentName = "";
        if (ddlLanguage.Visible == false)
        {
            AgentName = AgentNameTH;
        }
        else if (ddlLanguage.Text == "1")
        {
            AgentName = AgentNameTH;
        }
        else if (ddlLanguage.Text == "2")
        {
            AgentName = AgentNameEN;
        }
        return AgentName;
    }

}
